<?php
class C_Refactor extends Controller {

	var $template_mod;
	var $db;
	
	function C_Refactor($template_mod = "general") {
		parent::Controller();
		if (Celini::getCurrentController() == 'Country') {
			$menu =& Menu::getInstance();
		}
		$this->template_mod = $template_mod;

		$this->db = Celini::dbInstance();
	}
	
	function searchTableLocalization($value){
		$columnName = $value;
		$tableList=array(); 
		
		//table using column "NAME" for localization
		$sql = "SELECT 
					DISTINCT TABLE_NAME as name
    			FROM 
    				INFORMATION_SCHEMA.COLUMNS
    			WHERE 
    				COLUMN_NAME IN ('$columnName')
    				AND TABLE_SCHEMA='rhm_dev'";
		
		$rs = $this->db->Execute($sql);
		while(!$rs->EOF){
			$tableName = $rs->fields('name');
			$sqlCheckUsingLocalization = "
				SELECT 
					l.* 
				FROM 
					$tableName c 
					INNER JOIN  localization l ON c.$columnName = l.localization_id";
			$rsCheck = $this->db->Execute($sqlCheckUsingLocalization);
//			var_dump($tableName);
//			var_dump(!$rsCheck->EOF);
			if(!$rsCheck->EOF){
				array_push($tableList, $tableName);		
			}
			$rs->moveNext();
		}
		return $tableList;
	}

	function alterTable($table, $columName){
		
		$myFile = "refactor_localization.sql";
		$fh = fopen($myFile, 'a') or die("can't open file $myFile");
		
		$vnField = "vi_$columName";
		$enField = "en_$columName";
		$sql ="ALTER TABLE $table DROP $vnField;\n" . "ALTER TABLE $table ADD $vnField VARCHAR(255) NOT NULL;\n";;						

		$sql .="ALTER TABLE $table DROP $enField;\n" . "ALTER TABLE $table ADD $enField VARCHAR(255) NOT NULL;\n";				
		fwrite($fh, $sql);
		fclose($fh);
		
		$dropFile = "drop_localization.sql";
        $fh = fopen($dropFile, 'a') or die("can't open file $dropFile");
        $sql = "ALTER TABLE $table DROP $columName; \n";        
        fwrite ($fh, $sql);
        fclose($fh);
	}
	
	function insertData($table, $columName){
		
		$myFile = "refactor_localization.sql";
		$fh = fopen($myFile, 'a') or die("can't open file");
		
		$vnField = "vi_$columName";
		$enField = "en_$columName";
		
		$sqlJoin = "SELECT l.* 
				FROM $table c INNER JOIN  localization l 
					ON c.$columName = l.localization_id";
		
		$rs = $this->db->Execute($sqlJoin);
		$count = 0;
		while($rs && !$rs->EOF){
			$vn = $rs->fields['vi_VN']; 
			$en	= $rs->fields['en_US'];
			$id = $rs->fields['localization_id'];
			$sqlInsert = "UPDATE $table SET $vnField =\"" .$vn ."\", $enField =\"" . $en . "\"  WHERE $columName = $id;\n";
			fwrite($fh, $sqlInsert);
			$count ++;
			//$this->db->Execute($sqlInsert);	
			$rs->moveNext();
		}
		$rows = "-- Table $table\n-- column are: $vnField and $enField\n-- rows effect: $count\n--\n";
		echo $rows . "</br >";
		fwrite($fh, $rows);
		fclose($fh);
	}
			
	
	/**
	 * refactor the tables with name's column
	 * @return unknown_type
	 */
	function actionRefactorNameColumn(){
		$data = array (
			'career',
			'chronic_disease',
		    'composite_service',
			'country ',
			'department',
		    'diagnosis',
			'diploma',
			'district',
			'employee_type',
		    'encounter_type',
			'equipment_type',
			'equipment_unit ',
			'examination_type',
			'hospital ',
			'icd10 ',
			'icd10_chapter',
			'icd10_group',
			'lab_group',
			'lab_item_factor',
		    'lab_result',
			'medical_agent',
		    'medical_material',
			'medicine',
			'package',
		    'pay_insurance_support',
		    'pha_drug_store',
			'province',
			'rad_additional_service',
			'rad_group',
			'room',
		    'sale_program',
			'service',
			'service_group',
			'service_unit',
			'surgery_equipment',
		    'surgery_service_group',
		    'title',
			'ward',
		);        
		
		$columName = "name";
		foreach ($data as $table){			
			$this->alterTable($table, $columName);	
			$this->insertData($table, $columName);
		} 
	}
	
	
/**
	 * refactor the tables with title's column
	 * @return unknown_type
	 */
	function actionRefactorTitleColumn(){
		$data = array (
			'enumeration_value',
			'menu',
		);
		
		$columName = "title";
		foreach ($data as $table){			
			$this->alterTable($table, $columName);	
			$this->insertData($table, $columName);
		} 
	}
	
	/**
	 * refactor the tables with title's column
	 * @return unknown_type
	 */
	function actionRefactorDescirptionColumn(){
		$data = array (
			'department',
			'room',
		    'sale_program'
		);
		$data = array (            
            'sale_program'
        );
		$columName = "description";
		foreach ($data as $table){			
			$this->alterTable($table, $columName);	
			$this->insertData($table, $columName);
		} 
	}
	
/**
	 * refactor the tables with title's column
	 * @return unknown_type
	 */
	function actionRefactorUsageTitleColumn(){
		$data = array (
			'medicine_default_usage',
		);
		
		$columName = "usage_title";
		foreach ($data as $table){			
			$this->alterTable($table, $columName);	
			$this->insertData($table, $columName);
		} 
	}
	
	
/**
	 * refactor the tables with usage_unit column
	 * @return unknown_type
	 */
	function actionRefactorUsageUnitColumn(){
		$data = array (
			'medicine_default_usage',
		);
		
		$columName = "usage_unit";
		foreach ($data as $table){			
			$this->alterTable($table, $columName);	
			$this->insertData($table, $columName);
		} 
	}
	
/**
	 * refactor the tables with QuantityTitle column
	 * @return unknown_type
	 */
	function actionRefactorQuantityTitleColumn(){
		$data = array (
			'medicine_default_usage',
		    'surgery_medical_record',
			'template_equipment_item',
		);
		
		$columName = "quantity_title";		
		foreach ($data as $table){
			$this->alterTable($table, $columName);	
			$this->insertData($table, $columName);
		} 
	}
	
	
/**
	 * refactor the tables with note column
	 * @return unknown_type
	 */
	function actionRefactorNoteColumn(){
		$data = array (
			'prescription_note',
		);
		
		$columName = "note";
		foreach ($data as $table){			
			$this->alterTable($table, $columName);	
			$this->insertData($table, $columName);
		} 
	}
	
	
	function actionRefactor(){
	    $myFile = "refactor_localization.sql";
        $fh = fopen($myFile, 'w') or die("can't open file");
        
        $sql = "SET NAMES UTF8;\n";
        fwrite($fh, $sql);
        fclose($fh);
        
        $dropFile = "drop_localization.sql";
        $fh = fopen($dropFile, 'w') or die("can't open file $dropFile");
        fwrite ($fh, "-- DROP columns and table 'localization';\n");
        fclose($fh);
        
		$this->actionRefactorNameColumn();
		$this->actionRefactorTitleColumn();
		
		$this->actionRefactorDescirptionColumn();
		$this->actionRefactorNoteColumn();
		
	    $this->actionRefactorQuantityTitleColumn();
		$this->actionRefactorUsageTitleColumn();
		$this->actionRefactorUsageUnitColumn();
		
        $fh = fopen($dropFile, 'a') or die("can't open file $dropFile");
        $sql = "DROP TABLE localization; \n";        
        fwrite ($fh, $sql);
        fclose($fh);
	}

}
?>
